package wiki;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

import com.Root;

/**
 * 
 * @author mona.chandrapal
 */
public class GetIssueList extends HttpServlet {

	/**
	 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
	 * methods.
	 * 
	 * @param request
	 *            servlet request
	 * @param response
	 *            servlet response
	 * @throws ServletException
	 *             if a servlet-specific error occurs
	 * @throws IOException
	 *             if an I/O error occurs
	 */
	protected void processRequest(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("application/json");
		PrintWriter out = response.getWriter();

		Root root = new Root();
		ResultSet rs = null, rscount = null;
		String sql, sql1 = "";
		JSONObject issue, issues;
		JSONArray issuelist = new JSONArray();
		JSONArray templist = new JSONArray();
		JSONObject datalist = new JSONObject();

		float numberofissues = 0;
		String pagenumber = null;
		String sortorder = null;
		String sortindex = null;
		String rows = null;
		int nrows = 0;
		String folderid = "0";
		String filter = "";
		Boolean searchoption = false;
		String search = "";
		String issuetitle = "";
		String version = "";
		String bugid = "";
		String createdby = "";

		PreparedStatement pst = null;
		try {

			// pagenumber="1";
			// sortorder="asc";
			// sortindex="id";
			rows = "10";
			pagenumber = request.getParameter("page");
			sortorder = request.getParameter("sord");
			sortindex = request.getParameter("sidx");
			folderid = request.getParameter("folderid");
			filter = request.getParameter("filters");
			search = request.getParameter("_search");

			//System.out.println("Filter: " + filter);

			// rows = request.getParameter("rows");

				if (sortorder.equalsIgnoreCase("")) {
					sortorder = "asc";
				}
				if (sortindex.equalsIgnoreCase("")) {
					sortindex = "issuetitle";
				}

			int npagenumber = Integer.parseInt(pagenumber);
			nrows = Integer.parseInt(rows);
			root.initConnection();

			int offset = 0;

			if (search.equalsIgnoreCase("False")) {
				sql = "select count(*) as count from issue where deleteflag =0 and published='1'  and moduleid ="
						+ folderid
						+ "  or categoryid = "
						+ folderid
						+ " or subcategoryid=" + folderid;

				if (folderid == null) {
					sql = "select count(*) as count from issue where deleteflag =0 and published='1'";

				}

				pst = root.con.prepareStatement(sql);

				//System.out.println(pst);

				rscount = pst.executeQuery();
				rscount.next();
				numberofissues = rscount.getInt("count");

				offset = (npagenumber - 1) * nrows;

				sql1 = "select * from issue where  deleteflag =0 and published='1' and (moduleid ="
						+ folderid + "  or categoryid = " + folderid + " or subcategoryid=" + folderid
						+ ") order by " + sortindex + " " + sortorder + "  limit " + rows + " offset ?;";

				if (folderid == null) {
					sql1 = "select * from issue where  deleteflag =0 and published='1'  order by "
							+ sortindex + " " + sortorder + "  limit " + rows + " offset ?;";

				}
				PreparedStatement pst1 = root.con.prepareStatement(sql1);
				pst1.setInt(1, offset);
				//System.out.println(pst1);
				rs = pst1.executeQuery();
			} else {    
				
					// Search = TRUE
					//System.out.println("Search= True");
					version = request.getParameter("version");
					issuetitle = request.getParameter("issuetitle");
					bugid = request.getParameter("bugid");
					createdby = request.getParameter("createdby");
	
					// System.out.println("version: "+ version);
					// System.out.println("issuetitle: "+ issuetitle);
	
					if (version == null) {
						version = "%";
					} else {
						version = "%" + version + "%";
					}
	
					if (issuetitle == null) {
						issuetitle = "%";
					} else {
						issuetitle = "%" + issuetitle + "%";
					}
	
					if (bugid == null) {
						bugid = "%";
					} else {
						bugid = "%" + bugid + "%";
					}
					
					if (createdby == null) {
						createdby = "%";
					} else {
						createdby = "%" + createdby + "%";
					}
	
	
					sql = "select count(*) as count from issue where  version like ? and issuetitle like ? and bugid like ? and createdby like ? and deleteflag =0 and published='1' and (moduleid ="
							+ folderid+ "  or categoryid = " + folderid + " or subcategoryid=" + folderid
							+ ") order by "+ sortindex + " "
							+ sortorder +" ;";
	
					if (folderid == null) {
						sql = "select count(*) as count from issue where version like ? and issuetitle like ? and  bugid like ?and createdby like ? and deleteflag =0  and published='1' order by "
								+ sortindex	+ " " + sortorder + " ;";
	
					}
					pst = root.con.prepareStatement(sql);
	
					pst = root.con.prepareStatement(sql);
					pst.setString(1, version);
					pst.setString(2, issuetitle);
					pst.setString(3, bugid);
					pst.setString(4, createdby);
					//pst.setInt(5, offset);
	
					rscount = pst.executeQuery();
					rscount.next();
					
					
	
					numberofissues = rscount.getInt("count");
					
					offset = (npagenumber - 1) * nrows;
					//System.out.println("Search filter numberofissues= "+ numberofissues);
	
					PreparedStatement pst1 = null;
	
					if (issuetitle == "%") {
	
						sql1 = "select * from issue where  version like ? and issuetitle like ? and bugid like ? and createdby like ? and deleteflag =0 and published='1' and (moduleid ="
								+ folderid+ "  or categoryid = " + folderid + " or subcategoryid=" + folderid
								+ ") order by "+ sortindex + " "
								+ sortorder + "  limit " + rows + " offset ?;";
	
						if (folderid == null) {
							sql1 = "select * from issue where version like ? and issuetitle like ? and  bugid like ?and createdby like ? and deleteflag =0  and published='1' order by "
									+ sortindex	+ " " + sortorder + "  limit " + rows + " offset ?;";
	
						}
	
						//System.out.println("Issue title blank");
						pst1 = root.con.prepareStatement(sql1);
						pst1.setString(1, version);
						pst1.setString(2, issuetitle);
						pst1.setString(3, bugid);
						pst1.setString(4, createdby);
						pst1.setInt(5, offset);
	
					} else {
						
									//System.out.println("Folderid: " + folderid);
				
									if (folderid == null) {
				
										//System.out.println("\n Folderid: null");
										//System.out.println("offset" + offset);
										
										
										sql = "Select count(*) as count, "
												+ "MATCH (issuetitle) AGAINST (? in boolean mode) "
												+ "AS score FROM issue where MATCH (issuetitle) AGAINST (? in boolean mode) "
												+ "and deleteflag=0 and published='1' and version like ? and bugid like ? and createdby like  ? "
												+ " order by score desc ;";
				
										// sql =
										// "select count(*) as count from issue where  version like ? and issuetitle like ? and bugid like ? and deleteflag =0 and published=1";
										pst = root.con.prepareStatement(sql);
				
										pst.setString(1, issuetitle);
										pst.setString(2, issuetitle);
										pst.setString(3, version);
										pst.setString(4, bugid);
										pst.setString(5, createdby);
										
				
										//System.out.println("Count without folder sel : "	+ pst);
										
										
										rscount = pst.executeQuery();
										rscount.next();
										
										
				
										numberofissues = rscount.getInt("count");
										
										offset = (npagenumber - 1) * nrows;
										//System.out.println("Search filter numberofissues= "+ numberofissues);
				
										sql1 = "Select *, "
												+ "MATCH (issuetitle) AGAINST (? in boolean mode) "
												+ "AS score FROM issue where MATCH (issuetitle) AGAINST (? in boolean mode) "
												+ "and deleteflag=0 and published='1' and version like ? and bugid like ? and createdby like ?"
												+ " order by score desc limit " + rows
												+ " offset ?";
				;
										// sql =
										// "select count(*) as count from issue where  version like ? and issuetitle like ? and bugid like ? and deleteflag =0 and published=1";
										pst1 = root.con.prepareStatement(sql1);
				
									
				
										pst1.setString(1, issuetitle);
										pst1.setString(2, issuetitle);
										pst1.setString(3, version);
										pst1.setString(4, bugid);
										pst1.setString(5, createdby);
										pst1.setInt(6, offset);
				
										//System.out.println("result without folder sel : "	+ pst1);
										
										
									} else {
										
										offset = (npagenumber - 1) * nrows;
										//System.out.println("offset" + offset);
										//System.out.println("Folderid: " + folderid);
										
										sql = "Select count(*) as count, "
												+ "MATCH (issuetitle) AGAINST (? in boolean mode) "
												+ "AS score FROM issue where MATCH (issuetitle) AGAINST (? in boolean mode) "
												+ "and deleteflag=0 and published='1' and version like ? and bugid like ? and createdby like ? and (moduleid ="
												+ folderid + "  or categoryid = " + folderid
												+ " or subcategoryid=" + folderid
												+ ") ;";
				
										pst = root.con.prepareStatement(sql);
				
										pst.setString(1, issuetitle);
										pst.setString(2, issuetitle);
										pst.setString(3, version);
										pst.setString(4, bugid);
										pst.setString(5, createdby);
										
										
										
										
										rscount = pst.executeQuery();
										rscount.next();
										numberofissues = rscount.getInt("count");
				
										//System.out.println("Count: " + numberofissues);
										
										
										
										
				
										sql1 = "Select *, "
												+ "MATCH (issuetitle) AGAINST (? in boolean mode) "
												+ "AS score FROM issue where MATCH (issuetitle) AGAINST (? in boolean mode) "
												+ "and deleteflag=0 and published='1' and version like ? and bugid like ? and createdby like ? and (moduleid ="
												+ folderid + "  or categoryid = " + folderid
												+ " or subcategoryid=" + folderid
												+ ") order by score desc limit " + rows
												+ " offset ?";
				
										pst1 = root.con.prepareStatement(sql1);
				
										
				
										pst1.setString(1, issuetitle);
										pst1.setString(2, issuetitle);
										pst1.setString(3, version);
										pst1.setString(4, bugid);
										pst1.setString(5, createdby);
										pst1.setInt(6, offset);
				
										//System.out.println("result with folder sel : " + pst1);
									}
	
					}
				rs = pst1.executeQuery();

			}

			int count = 0;

			while (rs.next()) {
				count++;
				issue = new JSONObject();
				issues = new JSONObject();

				issues.put("id", rs.getString("id"));

				issuelist = new JSONArray();
				issuelist.add(rs.getString("id"));
				issuelist.add(rs.getString("issuetitle"));
				issuelist.add(rs.getString("version"));
				issuelist.add(rs.getString("bugid"));
				issuelist.add(rs.getString("createdby"));

				issues.put("cell", issuelist);
				// System.out.println("issues: " + issues);
				templist.add(issues);

			}
			// System.out.println("tempuserlist: " + templist);

			float numberofpages=0;
			numberofpages = (numberofissues / nrows);
			//System.out.println("numberofissues= "+ numberofissues);
			datalist.put("total", Math.ceil(numberofpages));
			datalist.put("page", npagenumber);
			datalist.put("records", numberofissues);
			datalist.put("rows", templist);

		} catch (Exception e) {
			System.out.println("Exception getting issue: " + e.toString());
		} finally {
			try {
				rs.close();
				pst.close();
				root.closeConnection();
			} catch (Exception e) {

			}
		}

		out.println(datalist.toString());

	}

	// <editor-fold defaultstate="collapsed"
	// desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
	/**
	 * Handles the HTTP <code>GET</code> method.
	 * 
	 * @param request
	 *            servlet request
	 * @param response
	 *            servlet response
	 * @throws ServletException
	 *             if a servlet-specific error occurs
	 * @throws IOException
	 *             if an I/O error occurs
	 */
	@Override
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

	/**
	 * Handles the HTTP <code>POST</code> method.
	 * 
	 * @param request
	 *            servlet request
	 * @param response
	 *            servlet response
	 * @throws ServletException
	 *             if a servlet-specific error occurs
	 * @throws IOException
	 *             if an I/O error occurs
	 */
	@Override
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

	/**
	 * Returns a short description of the servlet.
	 * 
	 * @return a String containing servlet description
	 */
	@Override
	public String getServletInfo() {
		return "Short description";
	}// </editor-fold>
}
